import pymysql
import plotly.express as px

import pandas as pd

db_config={
        'host': '192.168.31.134',
        'user': 'root',
        'password': 'Password123@mysql',
        'database': 'oecd_data'
    }


connection = pymysql.connect(
    host=db_config.get('host', 'localhost'),
    user=db_config.get('user', 'root'),
    password=db_config.get('password', ''),
    database=db_config.get('database', 'test'),
    charset='utf8mb4'
)

cursor = connection.cursor()

gdp_sql = '''
SELECT country_name, time_period, obs_value, unit_multiplier, currency FROM `gdp_output_approach` where activity = '_T'
-- and time_period = '2022'
and price_base = 'V'
and unit_measure = 'XDC'
and `transaction` = 'B1G'
and observation_status = 'Normal value'
'''

gfcf_house_sql = '''
SELECT country_name, time_period, obs_value, unit_multiplier, currency FROM capital_formation_by_activity
WHERE activity = '_T'
-- and time_period = '2022'
and instr_asset = 'N111G'
and unit_measure = 'XDC'
and price_base = 'V'
'''

per_capita_gpp_sql = '''
SELECT country_name, time_period, obs_value FROM `gdp_consumption_per_capita`
WHERE
  sector = 'S1'
AND
  transaction_2 = 'Gross domestic product, per capita'
'''

df_gdp = pd.read_sql(gdp_sql, connection)
print('>>> 顺利加载gdp数据')
df_gfcf_house = pd.read_sql(gfcf_house_sql, connection)
print('>>> 顺利加载gfcf_house数据')
df_per_capita_gdp = pd.read_sql(per_capita_gpp_sql, connection)
print('>>> 顺利加载per_capita_gpp数据')

connection.close()


# 方法2：使用交集
common_keys = set(zip(df_gdp['country_name'], 
                      df_gdp['time_period'])) & \
              set(zip(df_gfcf_house['country_name'], 
                      df_gfcf_house['time_period']))

# 过滤出共同的行
df_gdp_common = df_gdp[df_gdp.apply(lambda row: (row['country_name'], row['time_period']) in common_keys, axis=1)]
df_gfcf_house_common = df_gfcf_house[df_gfcf_house.apply(lambda row: (row['country_name'], row['time_period']) in common_keys, axis=1)]
df_per_capita_gdp_common = df_per_capita_gdp[df_per_capita_gdp.apply(lambda row: (row['country_name'], row['time_period']) in common_keys, axis=1)]
# 基于country_name和time_period进行合并
df_merged = df_gdp_common.merge(df_gfcf_house_common, 
                               on=['country_name', 'time_period'], 
                               suffixes=('_gdp', '_house'))

df_merged['ratio'] = 100 * df_merged['obs_value_house'] / df_merged['obs_value_gdp']

df_out = df_merged[['country_name', 'time_period', 'ratio']]
df_with_per_capita_gdp = df_out.merge(df_per_capita_gdp_common, 
                               on=['country_name', 'time_period'], 
                               suffixes=('_gdp', '_per_capita_gdp'))
df_with_per_capita_gdp.to_excel('df_with_per_capita_gdp.xlsx', index=False)
duplicates = df_out.duplicated(subset=['country_name', 'time_period']).sum()
print(f"重复数据行数: {duplicates}")

if duplicates > 0:
    print("存在重复数据，将使用平均值聚合:")
    # 使用平均值处理重复值
    df_pivot_agg = df_out.pivot_table(
        index='country_name', 
        columns='time_period', 
        values='ratio',
        aggfunc='mean',  # 对重复值取平均值
        fill_value=None
    )
    print(df_pivot_agg.head())
else:
    print("没有重复数据，直接使用pivot_table")
    df_pivot_agg = df_out.pivot_table(
        index='country_name', 
        columns='time_period', 
        values='ratio',
        aggfunc='first',
        fill_value=None
    )
    print(df_pivot_agg.head())


df_scatter = df_pivot_agg.reset_index().melt(
    id_vars=['country_name'], 
    var_name='time_period', 
    value_name='ratio'
)

# 删除缺失值
df_scatter = df_scatter.dropna()

# 确保时间周期是数值类型
df_scatter['time_period'] = df_scatter['time_period'].astype(int)

print(f"散点图数据行数: {len(df_scatter)}")
print(f"包含国家数: {df_scatter['country_name'].nunique()}")
print(f"时间范围: {df_scatter['time_period'].min()} - {df_scatter['time_period'].max()}")

# 使用散点图模式，显示标记和线条
fig_scatter_lines = px.scatter(
    df_scatter, 
    x='time_period', 
    y='ratio',
    color='country_name',
    title='各国住宅投资占GDP比例随时间变化（散点连线图）',
    labels={
        'time_period': '年份',
        'ratio': '住宅投资占GDP比例（%）',
        'country_name': '国家'
    },
    hover_data=['country_name', 'time_period', 'ratio'],
    symbol='country_name'  # 使用不同的标记符号
)

# 添加连线
fig_scatter_lines.update_traces(
    mode='markers+lines',
    line=dict(width=2),
    marker=dict(size=8)
)

fig_scatter_lines.update_layout(
    xaxis_title='年份',
    yaxis_title='住宅投资占GDP比例（%）',
    legend_title='国家',
    showlegend=True
)

# 保存图表为HTML文件
fig_scatter_lines.write_html('../住宅投资占比折线图.html')